package com.zz.repository;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import com.zz.entity.Deliverylist;

public interface DeliverylistRepository extends JpaRepository<Deliverylist,String>{
		//对入库单进行分页
		@Query(value="select del.id,pr.name as productname,del.out_num,del.out_time,s.name as gysname,w.name as ckname,del.notes from deliverylist as del left join product pr on (del.product_id=pr.id) left join supplier s on (del.supplier_id=s.id) left join ware w on (del.ware_id=w.id)",
				countQuery = "SELECT count(*) FROM deliverylist",
				nativeQuery = true)
		public Page<Object[]> showAllDeliverylist(Pageable pageable);
		
		
		//最近一周
		@Query(value="select del.id,pr.name as productname,del.out_num,del.out_time,s.name as gysname,w.name as ckname,del.notes from deliverylist as del left join product pr on (del.product_id=pr.id) left join supplier s on (del.supplier_id=s.id) left join ware w on (del.ware_id=w.id) where date_sub(curdate(),interval 7 day) <= DATE(del.out_time)",
				countQuery = "SELECT count(*) FROM deliverylist where date_sub(curdate(),interval 7 day) <= DATE(out_time)",
				nativeQuery = true)
		public Page<Object[]> showDeliverylistBy7day(Pageable pageable);
		//最近一个月
		@Query(value="select del.id,pr.name as productname,del.out_num,del.out_time,s.name as gysname,w.name as ckname,del.notes from deliverylist as del left join product pr on (del.product_id=pr.id) left join supplier s on (del.supplier_id=s.id) left join ware w on (del.ware_id=w.id) where date_sub(curdate(),interval 30 day) <= DATE(del.out_time)",
				countQuery = "SELECT count(*) FROM deliverylist where date_sub(curdate(),interval 30 day) <= DATE(out_time)",
				nativeQuery = true)
		public Page<Object[]> showDeliverylistBy30day(Pageable pageable);
		//一段时间查询
		@Query(value="select del.id,pr.name as productname,del.out_num,del.out_time,s.name as gysname,w.name as ckname,del.notes from deliverylist as del left join product pr on (del.product_id=pr.id) left join supplier s on (del.supplier_id=s.id) left join ware w on (del.ware_id=w.id) where del.out_time between ?1 and ?2",
				countQuery = "SELECT count(*) FROM deliverylist where out_time between ?1 and ?2",
				nativeQuery = true)
		public Page<Object[]> showDeliverylistBytime(String starttime,String lasttime,Pageable pageable);
}
